package com.koo.modules.quartz.constant;

public enum B2BConstant {
    
    /**yaodian数据库前缀 -- 门店正式*/
    YAODIAN("yaodian"),
    /**middle数据库前缀 -- 门店过渡*/
    /**member数据库前缀 -- 会员*/
    MEMBER("member"),
    /**admin数据库前缀 -- 监控端*/
    ADMIN("admin"),
    
    /**广缘标识 (companyCode) = gy */
    GY("gy"),
    
    /** 广缘下拉门店信息 (name,sql) <br>
     * 查询字段:storeCode,storeType,storeName,contactor,tel, <br>
     * --|storeAddress,status,companyId,createTime, updateTime <br>
     * 查询表:GL_Custom <br>
     * 排序:UpdateDate <br>
     * 条件拼装 :{WHERE_LIST} --> WHERE UpdateDate >= '"+addDate+"' 
     */
    GY_PULL_STORE_SQL("广缘下拉门店信息sql","SELECT tjbh AS storeCode, dwlb AS storeType, mc AS storeName, lxr AS contactor, tel AS tel, addr AS storeAddress, fdeleted AS status, CASE WHEN tj_tag = '1' THEN '0' ELSE '1' END AS companyId, UpdateDate AS createTime, GETDATE() AS updateTime FROM GL_Custom {WHERE_LIST} ORDER BY UpdateDate"),
    
    /**
     * 广缘下拉商品信息 (name,sql) <br>
     * 查询字段:categoryId, retailMiddleClass, retailSmallClass, productCluster, coreItemClass, <br>
     * --|seasonClass, storeRequireClass, isEan2, isEan1, otcClass, <br>
     * --|isDangerChemistry, instrumnetTypeId, conserveMeasure, characterInfo, effect, <br>
     * --|standardCode, proviceStandard, propertyId, conserveReason, drugCategoryId, <br>
     * --|categoryName, mnemonicName, productCode, barCode, productName, <br>
     * --|genericName, standard, producingArea, dosageForm, unit, <br>
     * --|propertyName, lastPurchasePrice, tradePrice, sellPrice1, sellPrice2, <br>
     * --|sellPrice3, retailPrice, realPrice, referenceRetailPrice, approvalNumber, <br>
     * --|produceFactory, acceptanceLevel, storageCondition, isRxDrug, deliveryRequireClass, <br>
     * --|isDrug, isImportedDrug, isDrugPiece, isTermination, isEggPreparation, <br>
     * --|isEphedrine, isThormone, isTwoClassmentalDrug, isMindDrug, isAnesthetic, <br>
     * --|isDiagnosticReagent, isMedicalToxicity, isStimulant, isVaccine, isHealthFood, <br>
     * --|isFood, lowTemperature, isGMP, isYiBao, expiryDate, <br>
     * --|gspTypeId, memberPrice, isMainConserve, mainClassId, lastSuppliyCompany, <br>
     * --|updateTime, createTime, materieldescawake, arearangeName, status<br>
     * 查询表:Yw_Kck, kclb, gl_super, dataitem, GLSX <br>
     * 排序:A.updateDate, A.HH <br>
     * 条件拼装 :{WHERE_LIST} --> WHERE A.updateDate >= '"+addDate+"'
     */
    //GY_PULL_PRODUCT_SQL("广缘下拉商品信息sql","SELECT TOP 2000 A.LB categoryId, A.FZFLSX2 retailMiddleClass, A.FZFLSX3 retailSmallClass, A.FZFLSX4 productCluster, A.CoreItemsClass coreItemClass, A.SeasonClass seasonClass, A.StoreSreQuireClass storeRequireClass, A.IsEan2 isEan2, A.IsEan1 isEan1, A.CFFLSX otcClass, A.IsDangerChemistry isDangerChemistry, A.MedicalInstrumentType instrumnetTypeId, A.YHCS conserveMeasure, A.c_xz characterInfo, A.c_gx effect, A.BZGG standardCode, A.BZ3 proviceStandard, A.SX propertyId, A.c_yhly conserveReason, A.SX drugCategoryId, B.Name categoryName, A.PM_ZJM mnemonicName, A.HH productCode, A.TM barCode, A.PM productName, A.BM genericName, A.GG standard, A.CD producingArea, A.jx dosageForm, A.PDW unit, a.sx +'-'+ isnull(s1.name, '') AS propertyName, CAST (A.jj AS DECIMAL(18, 2)) AS lastPurchasePrice, CAST (A.pfj AS DECIMAL(18, 2)) AS tradePrice, CAST (a.pfj1 AS DECIMAL(18, 2)) AS sellPrice1, CAST (a.lsj1 AS DECIMAL(18, 2)) AS sellPrice2, CAST (a.sj1 AS DECIMAL(18, 2)) AS sellPrice3, CAST (A.lsj AS DECIMAL(18, 2)) AS retailPrice, CAST (A.sj AS DECIMAL(18, 2)) AS realPrice, CAST (A.PJ AS DECIMAL(18, 2)) AS referenceRetailPrice, A.pzwh approvalNumber, A.scdw produceFactory, A.ysbz acceptanceLevel, A.cctj storageCondition, A.sfcf isRxDrug, A.RequireClass deliveryRequireClass, a.YPBZ isDrug, a.JKPZBZ isImportedDrug, a.ZYBZ isDrugPiece, a.ZZRS_TAG isTermination, a.ISEGGPREPARATION isEggPreparation, a.ISEPHEDRINE isEphedrine, a.ISTLHORMONE isThormone, a.ISTWOCLASSMENTALDRUG isTwoClassmentalDrug, a.ISMINDDRUG isMindDrug, a.ISANAESTHETIC isAnesthetic, a.ISDIAGNOSTICREAGENT isDiagnosticReagent, a.ISMEDICALTOXICITY isMedicalToxicity, a.ISSTIMULANT isStimulant, a.ISVACCINE isVaccine, a.IsHealthFoods isHealthFood, a.ISFOOD isFood, a.ISLOWTEMPERATURE lowTemperature, A.FIsGMP isGMP, A.FIsYBPZ isYiBao, A.xq expiryDate, A.GSPSortID gspTypeId, CAST (A.Hyj AS DECIMAL(18, 2)) AS memberPrice, A.c_zdyh isMainConserve, A.ZTFLSX mainClassId, a.dwbh +'-'+ c.mc AS lastSuppliyCompany, A.updateDate AS createTime, GETDATE() AS updateTime, materieldescawake materieldescawake, dbo.GetDataItemName ('318', AreaRangeDescibe) AS arearangeName, 1 as status FROM Yw_Kck A LEFT JOIN kclb B ON A.lb = B.id LEFT JOIN gl_super c ON a.dwbh = c.tjbh LEFT JOIN ( SELECT code, name FROM dataitem WHERE DATAITEMTYPEGUID = '310' ) l ON a.ABCFLSX = l.code LEFT JOIN ( SELECT GUID, FID, ID, NAME, BZ, FParentID, FLevel, FDetail, SXID FROM GLSX WHERE FID = 1 ) s1 ON a.sx = s1.id {WHERE_LIST} ORDER BY A.updateDate, A.HH"),
    GY_PULL_PRODUCT_SQL("广缘下拉商品信息sql","SELECT TOP 2000 A.LB categoryId,  A.CoreItemsClass coreItemClass, A.SeasonClass seasonClass, A.StoreSreQuireClass storeRequireClass, A.IsEan2 isEan2, A.IsEan1 isEan1, A.CFFLSX otcClass, A.IsDangerChemistry isDangerChemistry, A.MedicalInstrumentType instrumnetTypeId, A.YHCS conserveMeasure, A.c_xz characterInfo, A.c_gx effect, A.BZGG standardCode, A.BZ3 proviceStandard, A.SX propertyId, A.c_yhly conserveReason, A.SX drugCategoryId, B.Name categoryName, A.PM_ZJM mnemonicName, A.HH productCode, A.TM barCode, A.PM productName, A.BM genericName, A.GG standard, A.CD producingArea, A.jx dosageForm, A.PDW unit, a.sx +'-'+ isnull(s1.name, '') AS propertyName,  CAST (A.pfj AS DECIMAL(18, 2)) AS tradePrice, CAST (a.pfj1 AS DECIMAL(18, 2)) AS sellingPrice1, CAST (a.lsj1 AS DECIMAL(18, 2)) AS sellingPrice2, CAST (a.CLXS AS DECIMAL(18, 2)) AS sellingPrice3, CAST (A.lsj AS DECIMAL(18, 2)) AS retailPrice, A.pzwh approvalNumber, A.scdw produceFactory, A.ysbz acceptanceLevel, A.cctj storageCondition, A.sfcf isRxDrug, A.RequireClass deliveryRequireClass, a.YPBZ isDrug, a.JKPZBZ isImportedDrug, a.ZYBZ isDrugPiece, a.ZZRS_TAG isTermination, a.ISEGGPREPARATION isEggPreparation, a.ISEPHEDRINE isEphedrine, a.ISTLHORMONE isThormone, a.ISTWOCLASSMENTALDRUG isTwoClassmentalDrug, a.ISMINDDRUG isMindDrug, a.ISANAESTHETIC isAnesthetic, a.ISDIAGNOSTICREAGENT isDiagnosticReagent, a.ISMEDICALTOXICITY isMedicalToxicity, a.ISSTIMULANT isStimulant, a.ISVACCINE isVaccine, a.IsHealthFoods isHealthFood, a.ISFOOD isFood, a.ISLOWTEMPERATURE lowTemperature, A.FIsGMP isGMP, A.FIsYBPZ isYiBao, A.xq expiryDate, A.GSPSortID gspTypeId, CAST (A.Hyj AS DECIMAL(18, 2)) AS memberPrice, A.c_zdyh isMainConserve, A.ZTFLSX mainClassId, A.updateDate AS createTime, GETDATE() AS updateTime, dbo.GetDataItemName ('318', AreaRangeDescibe) AS arearangeName, 1 as status FROM Yw_Kck A LEFT JOIN kclb B ON A.lb = B.id LEFT JOIN gl_super c ON a.dwbh = c.tjbh LEFT JOIN ( SELECT code, name FROM dataitem WHERE DATAITEMTYPEGUID = '310' ) l ON a.ABCFLSX = l.code LEFT JOIN ( SELECT GUID, FID, ID, NAME, BZ, FParentID, FLevel, FDetail, SXID FROM GLSX WHERE FID = 1 ) s1 ON a.sx = s1.id {WHERE_LIST} ORDER BY A.updateDate, A.HH"),
//中药包装量修改语句
// GY_PULL_PRODUCT_SQL("广缘下拉商品信息sql","SELECT A.LB categoryId, A.FZFLSX2 retailMiddleClass, A.FZFLSX3 retailSmallClass, A.FZFLSX4 productCluster, A.CoreItemsClass coreItemClass, A.SeasonClass seasonClass, A.StoreSreQuireClass storeRequireClass, A.IsEan2 isEan2, A.IsEan1 isEan1, A.CFFLSX otcClass, A.IsDangerChemistry isDangerChemistry, A.MedicalInstrumentType instrumnetTypeId, A.YHCS conserveMeasure, A.c_xz characterInfo, A.c_gx effect, A.BZGG standardCode, A.BZ3 proviceStandard, A.SX propertyId, A.c_yhly conserveReason, A.SX drugCategoryId, B.Name categoryName, A.PM_ZJM mnemonicName, A.HH productCode, A.TM barCode, A.PM productName, A.BM genericName, A.GG standard, A.CD producingArea, A.jx dosageForm, A.PDW unit, a.sx +'-'+ isnull(s1.name, '') AS propertyName,  CAST (A.pfj AS DECIMAL(18, 2)) AS tradePrice, CAST (a.pfj1 AS DECIMAL(18, 2)) AS sellingPrice1, CAST (a.lsj1 AS DECIMAL(18, 2)) AS sellingPrice2, CAST (a.CLXS AS DECIMAL(18, 2)) AS sellingPrice3, CAST (A.lsj AS DECIMAL(18, 2)) AS retailPrice, A.pzwh approvalNumber, A.scdw produceFactory, A.ysbz acceptanceLevel, A.cctj storageCondition, A.sfcf isRxDrug, A.RequireClass deliveryRequireClass, a.YPBZ isDrug, a.JKPZBZ isImportedDrug, a.ZYBZ isDrugPiece, a.ZZRS_TAG isTermination, a.ISEGGPREPARATION isEggPreparation, a.ISEPHEDRINE isEphedrine, a.ISTLHORMONE isThormone, a.ISTWOCLASSMENTALDRUG isTwoClassmentalDrug, a.ISMINDDRUG isMindDrug, a.ISANAESTHETIC isAnesthetic, a.ISDIAGNOSTICREAGENT isDiagnosticReagent, a.ISMEDICALTOXICITY isMedicalToxicity, a.ISSTIMULANT isStimulant, a.ISVACCINE isVaccine, a.IsHealthFoods isHealthFood, a.ISFOOD isFood, a.ISLOWTEMPERATURE lowTemperature, A.FIsGMP isGMP, A.FIsYBPZ isYiBao, A.xq expiryDate, A.GSPSortID gspTypeId, CAST (A.Hyj AS DECIMAL(18, 2)) AS memberPrice, A.c_zdyh isMainConserve, A.ZTFLSX mainClassId, A.updateDate AS createTime, GETDATE() AS updateTime, dbo.GetDataItemName ('318', AreaRangeDescibe) AS arearangeName, 1 as status FROM Yw_Kck A LEFT JOIN kclb B ON A.lb = B.id LEFT JOIN gl_super c ON a.dwbh = c.tjbh LEFT JOIN ( SELECT code, name FROM dataitem WHERE DATAITEMTYPEGUID = '310' ) l ON a.ABCFLSX = l.code LEFT JOIN ( SELECT GUID, FID, ID, NAME, BZ, FParentID, FLevel, FDetail, SXID FROM GLSX WHERE FID = 1 ) s1 ON a.sx = s1.id where a.clxs is not null ORDER BY A.updateDate, A.HH"),

    /**
     * 广缘下拉出库单sql (name,sql) <br>
     * 查询字段:produceDate,headQuarterAllotNo,headQuarterAllotDate,keyNo,storeCode, <br>
     * --|productCode,productName,genericName,standard,producingArea, <br>
     * --|produceFactory,dosageForm,approvalNumber,unit,purchaseAmount, <br>
     * --|purchasePrice,purchaseFee,batchNo,expiryDate,remark,<br>
     * --|createUserName,outTypeId,createTime, updateTime, qualifiedAmount <br>
     * 查询表:fhdzk a, yw_kck k, FhdzkHead x <br>
     * 排序:a.jzrq, a.dh <br>
     * 条件拼装:{WHERE_LIST} --> AND a.jzrq > '"+addDate+"' <br>
     * 注意:addDate不能为空,为空查询全部数据!!!
     */
//    GY_PULL_OUT_ORDER_SQL("广缘下拉出库单sql","SELECT CONVERT (VARCHAR(10), a.SCRQ, 23) AS produceDate, a.lsh AS headQuarterAllotNo, CONVERT (VARCHAR(25), a.jzrq, 21) AS headQuarterAllotDate, a.dh AS keyNo, a.tjbh AS storeCode, a.hh AS productCode, k.pm AS productName, k.bm AS genericName, k.gg AS standard, k.cd AS producingArea, k.scdw AS produceFactory, k.jx AS dosageForm, k.pzwh AS approvalNumber, k.pdw AS unit, CAST (a.sl AS DECIMAL(18, 2)) AS purchaseAmount, a.sj AS purchasePrice, CAST (a.jshj AS DECIMAL(18, 2)) AS purchaseFee, a.ph1 AS batchNo, CONVERT (VARCHAR(10), a.ph1_xq, 23) AS expiryDate, x.notes AS remark, a.czry AS createUserName, CASE a.FTYpe WHEN 4 THEN 2 ELSE 1 END AS outTypeId, CONVERT (VARCHAR(25), a.jzrq, 120) AS createTime, GETDATE() AS updateTime, CAST (a.sl AS DECIMAL(18, 2)) AS qualifiedAmount FROM fhdzk a LEFT JOIN yw_kck k ON a.hh = k.hh LEFT JOIN FhdzkHead x ON a.lsh = x.Guid WHERE a.fsfcj != 1 {WHERE_LIST} ORDER BY a.jzrq, a.dh"),
//    GY_PULL_OUT_ORDER_SQL("广缘下拉出库单sql","SELECT CONVERT (VARCHAR(10), a.SCRQ, 23) AS produceDate, a.lsh AS headQuarterAllotNo, CONVERT (VARCHAR(25), a.jzrq, 21) AS headQuarterAllotDate, a.dh AS keyNo, a.tjbh AS storeCode, a.hh AS productCode, k.pm AS productName, k.bm AS genericName, k.gg AS standard, k.cd AS producingArea, k.scdw AS produceFactory, k.jx AS dosageForm, k.pzwh AS approvalNumber, k.pdw AS unit, CAST (a.sl AS DECIMAL(18, 2)) AS purchaseAmount, a.ZHDJ AS purchasePrice, CAST (a.jshj AS DECIMAL(18, 2)) AS purchaseFee, a.ph1 AS batchNo, CONVERT (VARCHAR(10), a.ph1_xq, 23) AS expiryDate, x.notes AS remark, a.czry AS createUserName, CASE a.FTYpe WHEN 4 THEN 2 ELSE 1 END AS outTypeId, CONVERT (VARCHAR(25), a.jzrq, 120) AS createTime, GETDATE() AS updateTime, CAST (a.sl AS DECIMAL(18, 2)) AS qualifiedAmount FROM fhdzk a LEFT JOIN yw_kck k ON a.hh = k.hh LEFT JOIN FhdzkHead x ON a.lsh = x.BillNo WHERE a.fsfcj != 1 {WHERE_LIST} ORDER BY a.jzrq, a.dh"),
    GY_PULL_OUT_ORDER_SQL("广缘下拉出库单sql","SELECT CONVERT (VARCHAR(10), a.SCRQ, 23) AS produceDate, a.lsh AS headQuarterAllotNo, CONVERT (VARCHAR(25), a.jzrq, 21) AS headQuarterAllotDate, a.dh AS keyNo, a.tjbh AS storeCode, a.hh AS productCode, k.pm AS productName, k.bm AS genericName, k.gg AS standard, k.cd AS producingArea, k.scdw AS produceFactory, k.jx AS dosageForm, k.pzwh AS approvalNumber, k.pdw AS unit, CAST (a.sl AS DECIMAL(18, 2)) AS purchaseAmount, (CASE when ISNULL(a.ZHDJ,0)=0 THEN a.sj ELSE a.ZHDJ END) AS purchasePrice, CAST (a.jshj AS DECIMAL(18, 2)) AS purchaseFee, a.ph1 AS batchNo, CONVERT (VARCHAR(10), a.ph1_xq, 23) AS expiryDate, x.notes AS remark, a.czry AS createUserName, CASE a.FTYpe WHEN 4 THEN 2 ELSE 1 END AS outTypeId, CONVERT (VARCHAR(25), a.jzrq, 120) AS createTime, GETDATE() AS updateTime, CAST (a.sl AS DECIMAL(18, 2)) AS qualifiedAmount FROM fhdzk a LEFT JOIN yw_kck k ON a.hh = k.hh LEFT JOIN FhdzkHead x ON a.lsh = x.BillNo WHERE a.fsfcj != 1 {WHERE_LIST} ORDER BY a.jzrq, a.dh"),
    
    /** BJA标识 (companyCode) = bja */
    BJA("bja"),
    
    /** 
     * BJA下拉门店信息 (name,sql) <br>
     * 查询字段:companyId, storeCode, storeType, storeName, storeFullName, <br>
     * --|contactor, storeAddress, createTime, updateTime, status <br>
     * 查询表:lsdtxb <br>
     * 排序:lastmodifytime <br>
     * 条件拼装 :{WHERE_LIST} --> AND lastmodifytime >= '"+addDate+"' 
     */
    BJA_PULL_STORE_SQL("BJA下拉门店sql","SELECT 'ZDA' AS companyId, duifbsh AS storeCode, 1 AS storeType, duifname AS storeName, duifname AS storeFullName, tw_mrxshy AS contactor, address AS storeAddress, lastmodifytime as createTime, GETDATE() as updateTime, 1 as status FROM lsdtxb WHERE duifbsh LIKE 'a%' {WHERE_LIST};"),

    /**
     * BJA下拉商品信息(name,sql)<br>
     * 查询字段:categoryName, productCode, productName, genericName, producingArea, <br>
     * --|standard, unit, mnemonicName, tradePrice, retailPrice, <br>
     * --|localRetailPrice, memberPrice, barCode, localBarCode, isRxDrug, <br>
     * --|approvalNumber, produceFactory, dosageForm, storageCondition, standardCode, <br>
     * --|proviceStandard, expiryDate, isYiBao, isGMP, effect, <br>
     * --|drugCategoryId, storageUpper, storageFloor, conserveMeasure, isDrugPiece, <br>
     * --|instrumnetTypeId, isHealthFood, isFood, createTime, updateTime <br>
     * --|status <br>
     * 查询表:spkfk <br>
     * 排序:lastmodifytime, spbh <br>
     * 条件拼装 :{WHERE_LIST} --> WHERE lastmodifytime >= '"+addDate+"' 
     */
    BJA_PULL_PRODUCT_SQL("BJA下拉商品sql","SELECT TOP 5000 leibie AS categoryName, spbh AS productCode, spmch AS productName, tongym AS genericName, shpchd AS producingArea, shpgg AS standard, dw AS unit, zjm AS mnemonicName, jj AS tradePrice, lshj AS retailPrice, lshj AS localRetailPrice, huiytj AS memberPrice, sptm AS barCode, sptm AS localBarCode, CASE WHEN chuffl = '处方用药' THEN 1 ELSE 0 END AS isRxDrug, pizhwh AS approvalNumber, shengccj AS produceFactory, jixing AS dosageForm, chuctj AS storageCondition, bwm AS standardCode, baozhgg AS proviceStandard, CASE WHEN youxq LIKE '%个月' THEN SUBSTRING (youxq, 0, len(youxq) - 1) WHEN youxq LIKE '%月' THEN SUBSTRING (youxq, 0, len(youxq)) WHEN youxq LIKE '%-%' OR youxq = '.' OR youxq = '' THEN NULL ELSE youxq END AS expiryDate, CASE WHEN is_ybsp = '是' THEN 1 ELSE 0 END AS isYiBao, CASE WHEN is_gmp = '是' THEN 1 ELSE 0 END AS isGMP, zhuzlx AS effect, shangplx AS drugCategoryId, kcsx AS storageUpper, kcxx AS storageFloor, yanghffcsh AS conserveMeasure, CASE WHEN leibie = '中药饮片' THEN 0 ELSE 1 END AS isDrugPiece, CASE WHEN leibie = '医疗器械类' THEN 0 ELSE 1 END AS instrumnetTypeId, CASE WHEN leibie = '保健食品' THEN 0 ELSE 1 END AS isHealthFood, CASE WHEN leibie = '食品' THEN 0 ELSE 1 END AS isFood, lastmodifytime AS createTime, GETDATE() AS updateTime, 1 AS status FROM spkfk  {WHERE_LIST}  ORDER BY lastmodifytime, spbh;"),
    
    /**
     * BJA下拉出库单sql (name,sql) <br>
     * 查询字段:storeCode, headQuarterAllotDate, headQuarterAllotNo, keyNo,  <br>
     * --|outTypeId, productCode, productName, genericName, standard,  <br>
     * --|unit, producingArea, produceFactory, approvalNumber, dosageForm,  <br>
     * --|purchaseAmount, qualifiedAmount, purchasePrice, purchaseFee, batchNo,  <br>
     * --|produceDate, expiryDate, createUserName, createTime, updateTime <br>
     * 查询表:dbckhz z, dbckmx m, spkfk s <br>
     * 排序:z.kaiprq <br>
     * 条件拼装:{WHERE_LIST} --> and convert(varchar(100), z.kaiprq + ' ' + z.ontime, 120) > '"+addDate+"' <br>
     */
    BJA_PULL_OUT_ORDER_SQL("BJA下拉出库单sql","SELECT ( SELECT hwbh FROM huoweizl WHERE hw = z.drhw ) AS storeCode, z.kaiprq + ' ' + z.ontime AS headQuarterAllotDate, rtrim(z.djbh) AS headQuarterAllotNo, rtrim(z.djbh) + '_' + CONVERT (VARCHAR(8), m.dj_sn) AS keyNo, 1 AS outTypeId, s.spbh AS productCode, s.spmch AS productName, s.tongym AS genericName, s.shpgg AS standard, m.dw AS unit, s.shpchd AS producingArea, s.shengccj AS produceFactory, s.pizhwh AS approvalNumber, s.jixing AS dosageForm, m.shl AS purchaseAmount, m.shl AS qualifiedAmount, m.dbdj AS purchasePrice, m.dbje AS purchaseFee, m.pihao AS batchNo, m.baozhiqi AS produceDate, m.sxrq AS expiryDate, z.username AS createUserName, z.kaiprq + ' ' + z.ontime AS createTime, GETDATE() as updateTime FROM dbckhz z, dbckmx m, spkfk s WHERE m.djbh = z.djbh AND m.spid = s.spid AND z.djlx = '312' AND z.drhw IN ( SELECT hw FROM huoweizl WHERE hwbh IN (SELECT duifbsh FROM lsdtxb) ) {WHERE_LIST} ORDER BY z.kaiprq;"),
    
    /**
     * BJA下拉退货单sql (name,sql) <br>
     * 查询字段:storeCode, headQuarterAllotDate, headQuarterAllotNo, keyNo, <br>
     * --|outTypeId, productCode, productName, genericName, standard, <br>
     * --|unit, producingArea, produceFactory, approvalNumber, dosageForm, <br>
     * --|purcheAmount, qualifiedAmount, purchePrice, purcheFee, batchNo, <br>
     * --|produceDate, expiryDate, createUserName, createTime, updateTime, <br>
     * --|remark <br>
     * 查询表:dbhz m, dbmx z, spkfk s <br>
     * 排序:z.kaiprq <br>
     * 条件拼装:{WHERE_LIST} --> and convert(varchar(100), z.kaiprq + ' ' + z.ontime, 120) > '"+addDate+"' <br>
     */
    BJA_PULL_OUT_ORDER_BACK_SQL("BJA下拉退货单sql","SELECT ( SELECT hwbh FROM huoweizl WHERE hw = z.dchw ) AS storeCode, z.kaiprq + ' ' + z.ontime AS headQuarterAllotDate, rtrim(z.djbh) AS headQuarterAllotNo, rtrim(z.djbh) + '_' + CONVERT (VARCHAR(8), m.dj_sn) AS keyNo, 2 AS outTypeId, s.spbh AS productCode, s.spmch AS productName, s.tongym AS genericName, s.shpgg AS standard, m.dw AS unit, s.shpchd AS producingArea, s.shengccj AS produceFactory, s.pizhwh AS approvalNumber, s.jixing AS dosageForm, 0 - m.shl AS purchaseAmount, 0 - m.shl AS qualifiedAmount, m.dbdj AS purchasePrice, 0 - m.dbje AS purchaseFee, m.pihao AS batchNo, m.baozhiqi AS produceDate, m.sxrq AS expiryDate, z.username AS createUserName, z.kaiprq + ' ' + z.ontime AS createTime, GETDATE() AS updateTime, m.yuany AS remark FROM dbhz z, dbmx m, spkfk s WHERE m.djbh = z.djbh AND m.spid = s.spid AND z.dchw IN ( SELECT hw FROM huoweizl WHERE hwbh IN (SELECT duifbsh FROM lsdtxb) ) {WHERE_LIST} ORDER BY z.kaiprq;"),
    
    /**
     * BJA上传销售数据sql (name, sql) <br>
     * 查询字段:saleOrderId, storeCode, saleOrderType, saleOrderNo, <br>
     * --|saleDate, memberNo, memberName, productCode, productName, <br>
     * --|genericName, producingArea, standard, unit, approvalNumber, <br>
     * --|produceFactory, dosageForm, expiryDate, produceDate, batchNo, <br>
     * --|lotNo, isRxDrug, isEphedrine, isGMP, isSplitDrug, <br>
     * --|saleCount, costPrice, costAmount, retailPrice, retailAmount, <br>
     * --|memberPrice, memberAmount, discount, discountFee, createTime, <br>
     * --|updateTime, operaterId, operater, remark <br>
     * 查询表:saleOrder so, saleOrderDetail sod, baseStoreInfo bsi <br>
     * 排序:bsi.storeCode <br>
     * 条件拼装:{WHERE_LIST} --> and sod.createTime > '"+addDate+"' <br>
     */
    BJA_UPLOAD_SALE_DATA_SQL("BJA上传销售数据sql","SELECT sod.saleOrderDetailId AS saleOrderId, so.storeId AS storeCode, saleOrderType, so.saleOrderNo, saleDate, memberNo, member AS 'memberName', productCode, productName, genericName, producingArea, standard, unit, approvalNumber, produceFactory, dosageForm, expiryDate, produceDate, batchNo, lotNo, sod.isRxDrug, sod.isEphedrine, isGMP, isSplitDrug, saleCount, costPrice, costAmount, retailPrice, retailAmount, memberPrice, memberAmount, discount, discountFee, sod.createTime, sod.updateTime, operaterId, operater, sod.remark FROM saleOrder so, saleOrderDetail sod WHERE so.saleOrderNo = sod.saleOrderNo AND so.storeId IN (SELECT storeId FROM baseSupplier WHERE dataDownloadCode = 'BJA') {WHERE_LIST} ORDER BY so.storeId ;"),
    
    /**
     * BJA上传库存数据sql (name, sql) <br>
     * 查询字段:inventoryId, storeCode, productCode, productName, <br>
     * --|genericName, standard, unit, producingArea, produceFactory, <br>
     * --|approvalNumber, dosageForm, produceDate, expiryDate, batchNo, <br>
     * --|stockCount, purchaseCount, purchasePrice, purchaseFee, operatorId, <br>
     * --|operator, inspectionOrderNo, lotNo, createTime, updateTime, <br>
     * --|remark <br>
     * 查询表:stockBatchInventory sbi, baseStoreInfo bsi <br>
     * 排序:bsi.storeCode <br>
     * 条件拼装:{WHERE_LIST} --> and updateTime > '"+addDate+"' <br>
     */
    BJA_UPLOAD_STOCK_DATA_SQL("BJA上传库存数据sql", "SELECT inventoryId, storeId AS storeCode, productCode, productName, genericName, standard, unit, producingArea, produceFactory, approvalNumber, dosageForm, produceDate, expiryDate, batchNo, stockCount, purchaseCount, purchasePrice, purchaseFee, operatorId, operator, inspectionOrderNo, lotNo, createTime, updateTime, remark FROM stockBatchInventory WHERE storeId IN (SELECT storeId FROM baseSupplier WHERE dataDownloadCode = 'BJA') {WHERE_LIST} order by storeId "),
    
    /**
     * BJA上传会员数据sql (name, sql) <br>
     * 查询字段:memberId, memberNo, memberName, memberType, <br>
     * --|sex, age, birthday, mobile, totalScore, <br>
     * --|storedMoney, medicalHistory, lastExpenseDate1, lastExpenseDate2, createUserName, <br>
     * --|createTime, updateTime, remark, storeCode, webchart, <br>
     * --|idCard, address, remainScore, auditState <br>
     * 查询表:memberInfo info, memberScore score, memberStoreShip ship <br>
     * 排序:ship.storeId <br>
     * 条件拼装:{STORE_IDS} --> 'id_1','id_2',...'id_n' <br>
     *        {WHERE_LIST} --> and info.updateTime > '"+addDate+"' <br>
     */
    BJA_UPLOAD_MEMBER_DATA_SQL("BJA上传会员数据sql","SELECT info.memberId, memberNo, info.memberName, memberType, sex, age, birthday, info.mobile, totalScore, storedMoney, medicalHistory, lastExpenseDate1, lastExpenseDate2, createUserName, info.createTime, info.updateTime, info.remark, ship.storeId AS storeCode, webchat AS webchart, cardNo AS idCard, memberAddress AS address, usableScore AS remainScore, isUse AS auditState FROM memberInfo info LEFT JOIN memberScore score ON info.memberId = score.memberId, memberStoreShip ship WHERE info.memberId = ship.memberId AND score.mainId = ship.storeId AND ship.storeId IN ({STORE_IDS}) {WHERE_LIST} ORDER BY ship.storeId;"),

    /** CX标识 (companyCode) = cx */
    CX("cx"),

    /** 辰星下拉门店信息 (name,sql) <br>
     * 查询字段:storeCode,storeName,contactor,tel,storeAddress, <br>
     * --|createTime, updateTime <br>
     * 查询表:_vhymclient <br>
     * 排序:addTime <br>
     * 条件拼装 :{WHERE_LIST} --> WHERE CONVERT (VARCHAR(25), addTime, 120) >= '"+addDate+"'
     */
    CX_PULL_STORE_SQL("辰星下拉门店信息sql","SELECT client_id AS store_code, alias AS store_name, qyfzr AS contactor, phone AS tel, address AS store_address, addTime AS create_time, GETDATE() AS update_time FROM _vhymclient {WHERE_LIST} ORDER BY addTime"),

    /**
     * 没有, 从出库单里面取一些字段作为商品资料
     */
    //CX_PULL_PRODUCT_SQL("辰星下拉商品信息sql",""),

    /**
     * 辰星下拉出库单sql (name,sql) <br>
     * 查询字段:produceDate,headQuarterAllotNo,headQuarterAllotDate,keyNo,storeCode, <br>
     * --|productCode,productName,genericName,standard,producingArea, <br>
     * --|produceFactory,dosageForm,approvalNumber,unit,purchaseAmount, <br>
     * --|purchasePrice,purchaseFee,batchNo,expiryDate,outTypeId, <br>
     * --|createTime, updateTime, qualifiedAmount, barCode(用于商品资料) <br>
     * 查询表:_vhymsale <br>
     * 排序:单据日期, 订单id <br>
     * 条件拼装:{WHERE_LIST} --> WHERE 订单id > '"+maxOrderId+"' <br>
     * 注意:maxOrderId不能为空,为空查询全部数据!!!
     */
    CX_PULL_OUT_ORDER_SQL("辰星下拉出库单sql","SELECT CONVERT (VARCHAR(10), 生产日期, 23) AS produce_date, 订单id AS head_quarter_allot_no, CONVERT (VARCHAR(25), 单据日期, 21) AS head_quarter_allot_date, smb_id AS key_no, 门店ID AS store_code, 产品id AS product_code, 品名 AS product_name, 品名 AS generic_name, 规格 AS standard, 产地 AS producing_area, 生产厂家 AS produce_factory, 剂型 AS dosage_form, 批准文号 AS approval_no, 单位 AS unit, CAST (数量 AS DECIMAL(18, 2)) AS purchase_amount, CAST (单价 AS DECIMAL(18, 2)) AS purchase_price, CAST (金额 AS DECIMAL(18, 2)) AS purchase_fee, 批号 AS batch_no, CONVERT (VARCHAR(10), 有效期, 23) AS expiry_date, 1 AS out_type, CONVERT (VARCHAR(25), 单据日期, 120) AS create_time, GETDATE() AS update_time, CAST (数量 AS DECIMAL(18, 2)) AS qualified_amount, ( SELECT TOP 1 barcode FROM _vhympbarcode WHERE p_id = s.[产品id] AND len(barcode) > 10) AS bar_code FROM _vhymsale s {WHERE_LIST} ORDER BY 单据日期, 订单id"),

    /**
     * 同步时一次最大插入数量. (name,number) <br>
     */
    MAX_INSERT_SIZE("同步出库单时一次最大插入数量.", 500),
    
    /**
     * 下载一次最大limit (name,number) <br>
     */
    MAX_LIMIT_SIZE("下载商品一次最大limit.", 1000),
    
    /**
     * 转换出库单时查询主单一次最大limit. (name,number) <br>
     */
    MAX_CONVERT_OUT_ORDER_LIMIT_SIZE("转换出库单时查询主单一次最大limit.", 200),
    
    /**
     * 根据下载标识识别门店使用的门店id sql (name, sql) <br>
     * 查询字段:storeId <br>
     * 查询表:baseSupplier <br>
     * 条件拼装:{DOWNLOAD_CODE} -> '"+downloadCode+"' <br>
     */
    SELECT_STORE_ID_SQL("根据下载标识识别门店使用的门店id","SELECT store_id FROM base_supplier WHERE data_download_code = '{DOWNLOAD_CODE}' "),
    
    /*
     * 通用下载商品sql(name,sql) <br>
     * 查询字段:xxBaseProduct 全部字段 <br>
     * 排序:createTime, productId <br>
     * 条件拼装:{PREFIX} --> xxPrefix , <br>
     * {WHERE_LIST} --> where (createTime = '" + addDate + "' and productId > '"+productId+"' ) or createTime > '" + addDate + "',<br>
     * {LIMIT} --> LIMIT 500
    DEFAULT_DOWNLOAD_STORE_SQL("通用门店下载商品sql","select * from {PREFIX}BaseProduct {WHERE_LIST} order by createTime, productId {LIMIT}"),
     */
    ;

    private String companyCode;
    private String sql;
    private String name;
    private Integer number;

    private B2BConstant(String companyCode) {
        this.companyCode = companyCode;
    }
    private B2BConstant(String name,String sql){
        this.name = name;
        this.sql = sql;
    }
    private B2BConstant(String name, Integer number){
        this.name = name;
        this.number = number;
    }

    public String getCompanyCode() {
        return this.companyCode;
    }
    public String getSql(){
        return this.sql;
    }
    public String getName(){
        return this.name;
    }
    public Integer getNumber(){
        return this.number;
    }

}
